﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace RadarMonitor.DB
{
    public partial class DBHelper
    {
        /*
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine("{0} {1} {2}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
            }
        }
        */

        public IList<object> lastRow(string tabel)
        {
            var result = new List<object>();
            try
            {
                var str = $@"SELECT TOP 1 * FROM {tabel} ORDER BY ID DESC";
                using (SqlCommand command = new SqlCommand(str, Connection))
                {
                    Connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            for(int index =0;index < reader.FieldCount;index++)
                            {
                                result.Add(reader[index]);
                            }
                        }
                    }
                }
            }catch(Exception ex)
            {
                Console.WriteLine(ex);
            }
            finally
            {
                try
                {
                    Connection.Close();
                }
                catch { }
            }
            return result;
            
        }

        public string lastedStatusAlarmRecord()
        {
            string result = null;
            try
            {
                var str = $@"SELECT TOP 1 * FROM Table_Monidor WHERE AlarmCode={(int)RadarMonitorResultCode.RDA_Alram} ORDER BY ID DESC";

                using (SqlCommand command = new SqlCommand(str, Connection))
                {
                    Connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            result = reader.GetString(4);
                        }
                    }
                }
            }
            catch { }
            finally { }

            return result;
        }

        public string TodaylastedStatusAlarmRecord()
        {
            string result = null;
            try
            {
                var str = $@"SELECT TOP 1 * FROM Table_Monidor WHERE AlarmCode={(int)RadarMonitorResultCode.RDA_Alram} AND DateDiff(dd,Time,getdate())=0  ORDER BY ID DESC";

                using (SqlCommand command = new SqlCommand(str, Connection))
                {
                    Connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            result = reader.GetString(4);
                        }
                    }
                }
            }
            catch { }
            finally { }

            return result;
        }

        public IList<ViewModel.logItem> logItemsAt(int start ,int length)
        {
            if (start < 0 || length <= 0)
            {
                throw new InvalidProgramException();
            }
            var result = new List<ViewModel.logItem>();

            try
            {
                var sqlStr = $"SELECT * FROM Table_Monidor ORDER BY Id desc OFFSET  {start} ROWS FETCH NEXT {length} ROWS ONLY";
                using (SqlCommand command = new SqlCommand(sqlStr, Connection))
                {
                    Connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string msg = null;
                            try { msg= reader.GetString(4); } catch { } //存入数据库的中文是乱码！！
                            var log = new ViewModel.logItem (
                                reader.GetInt32(0),
                                reader.GetInt32(1),
                                reader.GetDateTime(2),
                                reader.GetInt32(3),
                                null
                                );
                            result.Add(log);
                        }
                    }
                }
            }
            catch (Exception ex){
                Console.WriteLine(ex);
            }
            finally
            {
                try
                {
                    Connection.Close();
                }
                catch { }
            }

            return result;
        }

        public IList<ViewModel.logItem> logItemsAt(int start, int length, bool sourceEn, int source, bool STEn, DateTime startT, bool ETEn,  DateTime EndTime)
        {
            if (start < 0 || length <= 0)
            {
                throw new InvalidProgramException();
            }
            var result = new List<ViewModel.logItem>();

            var sqlStr = $"SELECT * FROM Table_Monidor";
            if (sourceEn || STEn || ETEn)
            {
                sqlStr += " WHERE ";

                bool add = false;

                if (sourceEn)
                {
                    sqlStr += $@"Source ={source} ";
                    add = true;
                }

               
                if (STEn)
                {
                    if (add)
                    {
                        sqlStr += " AND ";
                    }

                    sqlStr += $@"Time >='{startT}' ";
                    add = true;
                }

               

                if (ETEn)
                {
                    if (add)
                    {
                        sqlStr += " AND ";
                    }
                    sqlStr += $@"Time <= '{EndTime}' ";
                    add = true;
                }
            }

            sqlStr += $" ORDER BY Id desc OFFSET  {start} ROWS FETCH NEXT {length} ROWS ONLY";


            try
            {
                using (SqlCommand command = new SqlCommand(sqlStr, Connection))
                {
                    Connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string msg = null;
                            try { msg = reader.GetString(4); } catch { } //存入数据库的中文是乱码！！
                            var log = new ViewModel.logItem(
                                reader.GetInt32(0),
                                reader.GetInt32(1),
                                reader.GetDateTime(2),
                                reader.GetInt32(3),
                                null
                                );
                            result.Add(log);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            finally
            {
                try
                {
                    Connection.Close();
                }
                catch { }
            }

            return result;

        }

        public int RowCount(string tabel)
        {
            int cnt = 0;
            try
            {
                var str = $@"SELECT Count(*)  FROM {tabel}";
                using (SqlCommand command = new SqlCommand(str, Connection))
                {
                    Connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            cnt = reader.GetInt32(0);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            finally
            {
                try
                {
                    Connection.Close();
                }
                catch { }
            }

            return cnt;
        }

        public int RowCount(bool sourceEn, int source, bool STEn, DateTime startT, bool ETEn, DateTime EndTime)
        {
            int cnt = 0;
            var sqlStr = $@"SELECT Count(*)  FROM Table_Monidor";
            if (sourceEn || STEn || ETEn)
            {
                sqlStr += " WHERE ";

                bool add = false;

                if (sourceEn)
                {
                    sqlStr += $@"Source ={source} ";
                    add = true;
                }



                if (STEn)
                {
                    if (add)
                    {
                        sqlStr += " AND ";
                    }
                    sqlStr += $@"Time >='{startT}' ";
                    add = true;
                }



                if (ETEn)
                {
                    if (add)
                    {
                        sqlStr += " AND ";
                    }
                    sqlStr += $@"Time <= '{EndTime}' ";
                    add = true;
                }
            }
            try
            {
                
                using (SqlCommand command = new SqlCommand(sqlStr, Connection))
                {
                    Connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            cnt = reader.GetInt32(0);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            finally
            {
                try
                {
                    Connection.Close();
                }
                catch { }
            }

            return cnt;
        }
    }
}
